package com.PMSys.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.PMSys.Common.DbUtils;
import com.PMSys.Demain.DepartmentalMob;
import com.PMSys.Demain.Dimission;
import com.PMSys.Demain.PostMob;
import com.PMSys.Demain.Staff;

public class ReportManagementDaoImpl implements ReportManagementDao {

	private Connection conn;

	public ReportManagementDaoImpl() {

		this.conn = DbUtils.getConnection();
	}

	public List<Staff> selectNewEmplloyess(Date startDate,Date endDate) {
		// 创建sql语句
		String sql = "SELECT * FROM Staff WHERE s_entrydate >= ? AND s_entrydate <= ?";
		// 创建命令对象
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		List<Staff> allStaff = new ArrayList<Staff>();
		try {
			pst = conn.prepareStatement(sql);
			pst.setDate(1, new java.sql.Date(startDate.getTime()));
			pst.setDate(2, new java.sql.Date(endDate.getTime()));

			// 执行命令
			rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				Staff staff = new Staff();
				
				staff.setS_id(rs.getString("s_id"));
				staff.setS_name(rs.getString("s_name"));
				staff.setS_sex(rs.getString("s_sex"));
				staff.setS_birthday(rs.getString("s_birthday"));
				staff.setS_idCard(rs.getString("s_idCard"));
				staff.setD_id(rs.getString("d_id"));
				staff.setP_id(rs.getString("p_id"));
				staff.setS_entrydate(rs.getString("s_entrydate"));
				staff.setS_workdate(rs.getString("s_workdate"));
				staff.setS_workform(rs.getString("s_workform"));
				staff.setS_source(rs.getString("s_source"));
				staff.setS_political(rs.getString("s_political"));
				staff.setS_phone(rs.getString("s_phone"));
				staff.setS_oldOccupation_Startdate(rs.getString("s_oldOccupation_Startdate"));
				staff.setS_oldOccupation_Enddate(rs.getString("s_oldOccupation_Enddate"));
				staff.setS_oldCompany_name(rs.getString("s_oldCompany_name"));
				staff.setS_oldWork_content(rs.getString("s_oldWork_content"));
				staff.setS_foreign_languages(rs.getString("s_foreign_languages"));
				staff.setS_kinship(rs.getString("s_kinship"));
				staff.setS_relation_name(rs.getString("s_relation_name"));
				staff.setS_relation_telephone(rs.getString("s_relation_telephone"));
				staff.setS_sexName();
				staff.setS_kinshipName();
				staff.setS_politicalName();
				staff.setS_sourceName();
				staff.setS_workformName();
				
				allStaff.add(staff);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return allStaff;
	}
	
	public List<DepartmentalMob> selectDepartmentalMob(Date startDate,Date endDate) {
		// 创建sql语句
		String sql = "SELECT * FROM DepartmentalMob WHERE td_date >= ? AND td_date <= ?";
		// 创建命令对象
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		List<DepartmentalMob> departmentalMobList = new ArrayList<DepartmentalMob>();
		try {
			pst = conn.prepareStatement(sql);
			pst.setDate(1, new java.sql.Date(startDate.getTime()));
			pst.setDate(2, new java.sql.Date(endDate.getTime()));

			// 执行命令
			rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				DepartmentalMob departmentalMob = new DepartmentalMob();
				
				departmentalMob.setTd_id(rs.getString("td_id"));
				departmentalMob.setS_id(rs.getString("s_id"));
				departmentalMob.setD_oldId(rs.getString("d_oldId"));
				departmentalMob.setD_newId(rs.getString("d_newId"));
				departmentalMob.setTd_type(rs.getString("td_type"));
				departmentalMob.setTd_reason(rs.getString("td_reason"));
				departmentalMob.setTd_remark(rs.getString("td_remark"));
				departmentalMob.setTd_date(rs.getDate("td_date"));
				
				departmentalMobList.add(departmentalMob);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return departmentalMobList;
	}
	
	
	public List<PostMob> selectPostMob(Date startDate,Date endDate) {
		// 创建sql语句
		System.out.println("查询postmob");
		String sql = "SELECT * FROM PostMob WHERE tp_date >= ? AND tp_date <= ?";
		// 创建命令对象
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		List<PostMob> postMobList = new ArrayList<PostMob>();
		try {
			pst = conn.prepareStatement(sql);
			pst.setDate(1, new java.sql.Date(startDate.getTime()));
			pst.setDate(2, new java.sql.Date(endDate.getTime()));

			// 执行命令
			rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				PostMob postMob = new PostMob();
				
				postMob.setTp_id(rs.getString("tp_id"));
				postMob.setS_id(rs.getString("s_id"));
				postMob.setP_oldId(rs.getString("p_oldId"));
				postMob.setP_newId(rs.getString("p_newId"));
				postMob.setTp_type(rs.getString("tp_type"));
				postMob.setTp_reason(rs.getString("tp_reason"));
				postMob.setTp_remark(rs.getString("tp_remark"));
				postMob.setTp_date(rs.getString("tp_date"));
				
				postMobList.add(postMob);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return postMobList;
	}

	
	public List<Dimission> selectDimission(Date startDate,Date endDate) {

		// 创建sql语句
		System.out.println("查询Dimission");
		String sql = "SELECT * FROM Dimission WHERE dim_date >= ? AND dim_date <= ?";
		// 创建命令对象
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		List<Dimission> dimissionList = new ArrayList<Dimission>();
		try {
			pst = conn.prepareStatement(sql);
			pst.setDate(1, new java.sql.Date(startDate.getTime()));
			pst.setDate(2, new java.sql.Date(endDate.getTime()));

			// 执行命令
			rs = pst.executeQuery();
			// 把结果集中的数据取出来
			while (rs.next()) {
				Dimission dimission = new Dimission();
				
				dimission.setDim_id(rs.getString("dim_id"));
				dimission.setS_id(rs.getString("s_id"));
				dimission.setDim_date(rs.getString("dim_date"));
				dimission.setDim_type(rs.getString("dim_type"));
				dimission.setDim_direction(rs.getString("dim_direction"));
				dimission.setDim_remark(rs.getString("dim_remark"));
				
				
				dimissionList.add(dimission);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return dimissionList;
	}
	

}
